-- 默认在postgres数据库下进行操作
-- 创建task表
create table task(
  workflowInstanceKey BIGINT,
  jobKey BIGINT,
  name TEXT,
  description TEXT,
  variables TEXT,
  taskForm TEXT,
  formFields TEXT,
  assignee BIGINT,
  candidateGroup TEXT,
  endTime TIMESTAMPTZ,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (workflowInstanceKey, jobKey)
);


-- 创建task_claim表
create table task_claim(
  workflowInstanceKey BIGINT,
  jobKey BIGINT,
  assignee BIGINT,
  endTime TIMESTAMPTZ,
  state TEXT NOT NULL DEFAULT '已声明',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (workflowInstanceKey, jobKey)
);


-- 测试用的初始化数据,时间必须要大写
-- INSERT INTO task (workflowInstanceKey,jobKey,name,description,variables,taskForm,formFields,assignee,candidateGroup,endTime) VALUES (1,2,'a','AAA','','','',000001,'',CURRENT_TIMESTAMP );
-- INSERT INTO task (workflowInstanceKey,jobKey,name,description,variables,taskForm,formFields,assignee,candidateGroup,endTime) VALUES (1,3,'b','BBB','','','',000002,'',CURRENT_TIMESTAMP );
-- INSERT INTO task (workflowInstanceKey,jobKey,name,description,variables,taskForm,formFields,assignee,candidateGroup,endTime) VALUES (1,4,'c','CCC','','','',-1,'employee',CURRENT_TIMESTAMP );
-- INSERT INTO task_claim (workflowInstanceKey,jobKey,assignee,endTime) VALUES (1,2,000001,CURRENT_TIMESTAMP );
-- INSERT INTO task_claim (workflowInstanceKey,jobKey,assignee,endTime) VALUES (1,3,000002,CURRENT_TIMESTAMP );



-- 初始化函数声明
-- 将声明的任务完成, select操作必须声明一个变量存入一个select后的值【OK】
-- %是占位符，而不是%s
CREATE OR REPLACE FUNCTION humanTaskComplete(v_jobKey BIGINT)
    RETURNS VOID AS
$$
DECLARE
    v_result JSONB;
BEGIN
  SELECT json_build_object(
    'workflowInstanceKey',workflowInstanceKey,
    'jobKey',jobKey,
    'assignee',assignee,
    'endTime',cast(endTime as TIMESTAMP),
    'state',state
  ) INTO  v_result
  from task_claim WHERE jobKey=v_jobKey and state='已声明';

  IF NOT FOUND THEN
    RAISE EXCEPTION '404: task_claim表中不存在状态为已声明的记录:%', v_jobKey;
  END IF;

  update task_claim set state='已完成',updated_at = CURRENT_TIMESTAMP where jobKey=v_jobKey and state='已声明';
END;
$$ LANGUAGE PLPGSQL;


-- 员工声明认领任务【OK】
-- 数组的包含操作不好处理，切换成字符串的操作
-- 插入数据时，需要将从sql中查出的数据强制转化，否则默认为字符串类型
CREATE OR REPLACE FUNCTION humanTaskClaim(v_jobKey BIGINT,v_userId BIGINT,v_group JSONB)
    RETURNS VOID AS
$$
DECLARE
  taskClaimCount BIGINT;
  taskRecord JSONB;
BEGIN
  SELECT count(*) INTO taskClaimCount from task_claim WHERE jobKey=v_jobKey;
  IF taskClaimCount > 0 THEN
    RAISE EXCEPTION '400: task_claim表中已经存在记录:%s', v_jobKey;
  ELSE
    SELECT json_build_object(
      'workflowInstanceKey',workflowInstanceKey,
      'jobKey',jobKey,
      'name',name,
      'description',description,
      'variables',variables,
      'taskForm',taskForm,
      'formFields',formFields,
      'assignee',assignee,
      'candidateGroup',candidateGroup,
      'endTime',cast(endTime as TIMESTAMP)
    ) INTO taskRecord
    from task WHERE jobKey=v_jobKey;

    IF NOT FOUND THEN
      RAISE EXCEPTION '404: task表中不存在记录：%', v_jobKey;
    ELSE
      IF (v_group ->> 'employee' ~ CAST(v_userId as TEXT) ) THEN
        INSERT INTO task_claim (workflowInstanceKey,jobKey,assignee,endTime)
        VALUES (
          CAST(taskRecord ->>'workflowInstanceKey' AS BIGINT),
          CAST(taskRecord ->>'jobKey' AS BIGINT),
          CAST(v_userId AS BIGINT),
          CAST(taskRecord ->>'endTime' AS TIMESTAMP)
        );
      ELSE
        RAISE EXCEPTION '400: % task don''t belong group %', v_jobKey, v_group ->> 'employee';
      END IF;
    END IF;
  END IF;
END;
$$
LANGUAGE PLPGSQL;


-- 设置任务截止时间【OK】
CREATE OR REPLACE FUNCTION setHumanTaskEndTime(v_jobKey BIGINT,v_seconds BIGINT)
    RETURNS TEXT AS
$$
DECLARE
  v_result TEXT;
  taskClaimRecord JSONB;
  context TEXT;
BEGIN
  SELECT json_build_object(
    'workflowInstanceKey',workflowInstanceKey,
    'jobKey',jobKey,
    'name',name,
    'description',description,
    'variables',variables,
    'taskForm',taskForm,
    'formFields',formFields,
    'assignee',assignee,
    'candidateGroup',candidateGroup,
    'endTime',cast(endTime as TIMESTAMP)
  ) INTO v_result
  from task WHERE jobKey=v_jobKey;

  IF NOT FOUND THEN
    RAISE EXCEPTION '404: task表中不存在记录：%', v_jobKey;
  ELSE
    SELECT json_build_object(
      'workflowInstanceKey',workflowInstanceKey,
      'jobKey',jobKey,
      'assignee',assignee,
      'endTime',cast(endTime as TIMESTAMP),
      'state',state
    ) INTO taskClaimRecord
    from task_claim WHERE jobKey=v_jobKey;

    IF NOT FOUND THEN
      update task set endTime = to_timestamp(v_seconds),updated_at = CURRENT_TIMESTAMP where jobKey=v_jobKey;
      context := (v_jobKey,'人工任务修改截止时间成功');
    ELSIF taskClaimRecord ->>'state' = '已声明' THEN
      update task set endTime = to_timestamp(v_seconds), updated_at = CURRENT_TIMESTAMP where jobKey=v_jobKey;
      update task_claim set endTime = to_timestamp(v_seconds), updated_at = CURRENT_TIMESTAMP where jobKey=v_jobKey;
      context := (v_jobKey,'人工任务修改截止时间成功');
    ELSIF taskClaimRecord ->>'state' = '已完成' THEN
      RAISE EXCEPTION '400: % 任务已完成，不能修改截止时间', v_jobKey;
    END IF;
  END IF;

  RETURN context;
END;
$$
LANGUAGE PLPGSQL;


-- 分配任务
CREATE OR REPLACE FUNCTION assignTask(v_jobKey BIGINT,v_userId BIGINT,v_assignee BIGINT,v_userMap JSONB,v_group JSONB)
    RETURNS TEXT AS
$$
DECLARE
  context TEXT;
  taskClaimRecord JSONB;
  taskRecord JSONB;
  v_candidateGroup TEXT;
  userIdSet TEXT;
BEGIN
  SELECT json_build_object(
    'workflowInstanceKey',workflowInstanceKey,
    'jobKey',jobKey,
    'assignee',assignee,
    'endTime',cast(endTime as TIMESTAMP),
    'state',state
  ) INTO taskClaimRecord
  from task_claim WHERE jobKey=v_jobKey;

  IF NOT FOUND THEN
    SELECT json_build_object(
      'workflowInstanceKey',workflowInstanceKey,
      'jobKey',jobKey,
      'name',name,
      'description',description,
      'variables',variables,
      'taskForm',taskForm,
      'formFields',formFields,
      'assignee',assignee,
      'candidateGroup',candidateGroup,
      'endTime',cast(endTime as TIMESTAMP)
    ) INTO taskRecord
    from task WHERE jobKey=v_jobKey;

    IF NOT FOUND THEN
      RAISE EXCEPTION '404: task表中不存在记录：%', v_jobKey;
    ELSE
      IF (v_userMap ->>cast(v_userId AS TEXT)) = 'admin' THEN
        INSERT INTO task_claim (workflowInstanceKey,jobKey,assignee,endTime)
        VALUES (
          cast(taskRecord ->>'workflowInstanceKey' AS BIGINT),
          cast(taskRecord ->>'jobKey' AS BIGINT),
          v_assignee,
          cast(taskRecord ->>'endTime' AS TIMESTAMP)
        );
        context := (v_userId,'分配',v_jobKey,'任务到',v_assignee,'用户成功');
      ELSE
        RAISE EXCEPTION '400: only admin can assign init task';
      END IF;
    END IF;

  ELSIF taskClaimRecord ->>'state' = '已完成' THEN
    RAISE EXCEPTION '400: %任务已完成，不能再次声明', v_jobKey;

  ELSIF taskClaimRecord ->>'state' = '已声明' THEN
    IF v_userMap ->>cast(v_assignee AS TEXT) = 'admin' THEN
      IF v_userId = cast(taskClaimRecord ->>'assignee' AS BIGINT)  THEN
        update task_claim set assignee=v_assignee,updated_at = CURRENT_TIMESTAMP where jobKey=v_jobKey;
        context := (v_userId,'分配',v_jobKey,'任务到',v_assignee,'用户成功');
      ELSE
        RAISE EXCEPTION '400: task_claim表中不存在属于%的任务%',v_userId, v_jobKey;
      END IF;
    ELSE
      SELECT candidateGroup INTO v_candidateGroup from task WHERE jobKey=v_jobKey;
      IF NOT FOUND THEN
        RAISE EXCEPTION '404: task表中的%任务不存在', v_jobKey;
      ELSE
        userIdSet := v_group ->>v_candidateGroup;
        IF userIdSet = '[]' THEN
           userIdSet := '[0]';
        ELSE
		      userIdSet := (0, userIdSet);
        END IF;

        IF userIdSet ~ cast(v_assignee AS TEXT) THEN
          update task_claim set assignee=v_assignee,updated_at = CURRENT_TIMESTAMP where jobKey=v_jobKey;
          context := (v_userId,'分配',v_jobKey,'任务到',v_assignee,'用户成功');
        ELSE
          RAISE EXCEPTION '400: %用户不在任务%的分配范围内',v_assignee, v_jobKey;
        END IF;
      END IF;
    END IF;
  END IF;

  RETURN context;
END;
$$
LANGUAGE PLPGSQL;
